﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Abhiruchi_Phase1;

namespace EmployeeForm
{

    class OrganizationDAL
    {
        public static string CS = App.ConnectionString;
        public static void GetOrganization(string Eid, string Doj,String Designation, String ReportingTo, String RecruitedBy, String Qualifctn, string OtherQualif, string PrevEmp, string YearsOfExp, string RefferdBy, string RefferencePhNo
, string HighEduQualCertf, string PrevEmployerCertf, string EmpResume)
        {
            if (YearsOfExp == null || YearsOfExp == "")
            {
                YearsOfExp = "0";
            }

            Double Yoe = Double.Parse(YearsOfExp.ToString());


            SqlConnection mycon = new SqlConnection(CS);
            SqlCommand mycom = new SqlCommand();
            mycom.Connection = mycon;
            mycom.CommandText = "Sp_HRM_Get_Set_OrganizationalInfo";
            mycom.CommandType = CommandType.StoredProcedure;
            mycom.Parameters.AddWithValue("@Eid", Eid);
            mycom.Parameters.AddWithValue("@Doj",DateTime.Parse(Doj));
            mycom.Parameters.AddWithValue("@Designation", Designation);
            mycom.Parameters.AddWithValue("@ReportingTo",ReportingTo);
            mycom.Parameters.AddWithValue("@RecruitedBy", RecruitedBy);
            mycom.Parameters.AddWithValue("@Qualification", Qualifctn);
            mycom.Parameters.AddWithValue("@OtherQualification",OtherQualif);
            mycom.Parameters.AddWithValue("@PreviousEmployer",PrevEmp);
            mycom.Parameters.AddWithValue("@YearsOfExp",Yoe);
            mycom.Parameters.AddWithValue("@ReferredBy",RefferdBy);
            mycom.Parameters.AddWithValue("@ReferenceNumber", RefferencePhNo);
            mycom.Parameters.AddWithValue("@HighEduQualCertf", HighEduQualCertf);
            mycom.Parameters.AddWithValue("@PrevEmployerCertf", PrevEmployerCertf);
            mycom.Parameters.AddWithValue("@EmpResume", EmpResume);       
            mycom.Parameters.AddWithValue("@PType",2);                     
            using(mycon)
            {
                mycon.Open();
                mycom.ExecuteNonQuery();
            }
        }

        public static List<string> GetEmployees()
        {

            SqlConnection mycon = new SqlConnection(CS);
            SqlCommand mycom = new SqlCommand();
            mycom.Connection = mycon;
            mycom.CommandText = "Sp_HRM_Get_Set_OrganizationalInfo";
            mycom.CommandType = CommandType.StoredProcedure;
            mycom.Parameters.AddWithValue("@PType", 1);
            SqlDataAdapter myadp = new SqlDataAdapter(mycom);
            DataSet myds= new DataSet();
            myadp.Fill(myds);

            List<string> lstEmp = new List<string>();
            if (myds.Tables[0].Rows.Count > 0)
            {
                for(int i=0;i<myds.Tables[0].Rows.Count;i++)
                lstEmp.Add(myds.Tables[0].Rows[i][0].ToString());
            }

            return lstEmp;
        }
        public static List<EmpIdNames> GetEmployeeMasterEmployeeList()
        {

            List<EmpIdNames> lst=new List<EmpIdNames>();
            SqlConnection mycon = new SqlConnection(CS);
            SqlCommand mycom = new SqlCommand("SP_Get_Set_HRM_EmployeeMaster", mycon);
            mycom.CommandType = CommandType.StoredProcedure;
            mycom.Parameters.AddWithValue("@ptype", 6);
            SqlDataAdapter myAdp = new SqlDataAdapter(mycom);
            DataTable myDt = new DataTable();
            myAdp.Fill(myDt);
            if (myDt.Rows.Count > 0)
            {
                for (int i = 0; i < myDt.Rows.Count; i++)
                {
                    lst.Add(new EmpIdNames(myDt.Rows[i][0].ToString(), myDt.Rows[i][1].ToString()));
                    //lst[i].ID = (myDt.Rows[i][0].ToString());
                    //lst[i].NAME = (myDt.Rows[i][1].ToString());
                   
                }
            }

            return lst;
        }
        public static DataTable GetOrgantionUpdate(string Eid,string Doj, string Designation, string ReportingTo, string RecruitedBy, string Qualifctn, string OtherQualif, string PrevEmp, string YearsOfExp, string RefferdBy, string RefferencePhNo, string HighEduQualCertf, string PrevEmployerCertf, string EmpResume)
        {

            if (YearsOfExp == null || YearsOfExp == "")
            {
                YearsOfExp = "0";
            }

            Double Yoe = Double.Parse(YearsOfExp);

            SqlConnection mycon = new SqlConnection(CS);
            SqlCommand mycom = new SqlCommand();
            mycom.Connection = mycon;
            mycom.CommandText = "Sp_HRM_Get_Set_OrganizationalInfo";
            mycom.CommandType = CommandType.StoredProcedure;
            mycom.Parameters.AddWithValue("@Eid", Eid);
            mycom.Parameters.AddWithValue("@Doj", DateTime.Parse(Doj));
            mycom.Parameters.AddWithValue("@Designation", Designation);
            mycom.Parameters.AddWithValue("@ReportingTo", ReportingTo);
            mycom.Parameters.AddWithValue("@RecruitedBy", RecruitedBy);
            mycom.Parameters.AddWithValue("@Qualification", Qualifctn);
            mycom.Parameters.AddWithValue("@OtherQualification", OtherQualif);
            mycom.Parameters.AddWithValue("@PreviousEmployer", PrevEmp);
            mycom.Parameters.AddWithValue("@YearsOfExp", Yoe);
            mycom.Parameters.AddWithValue("@ReferredBy", RefferdBy);
            mycom.Parameters.AddWithValue("@ReferenceNumber", RefferencePhNo);
            mycom.Parameters.AddWithValue("@HighEduQualCertf", HighEduQualCertf);
            mycom.Parameters.AddWithValue("@PrevEmployerCertf", PrevEmployerCertf);
            mycom.Parameters.AddWithValue("@EmpResume", EmpResume);             
            mycom.Parameters.AddWithValue("@PType", 3);
            SqlDataAdapter myadp = new SqlDataAdapter(mycom);
            DataTable mydt = new DataTable();
            myadp.Fill(mydt);
            return mydt;
        }
        public static DataTable GetOrganizationUpdate(string Eid)
        {
            SqlConnection mycon = new SqlConnection(CS);
            SqlCommand mycom = new SqlCommand();
            mycom.Connection = mycon;
            mycom.CommandText = "Sp_HRM_Get_Set_OrganizationalInfo";
            mycom.CommandType = CommandType.StoredProcedure;
            mycom.Parameters.AddWithValue("@Eid", Eid);
            mycom.Parameters.AddWithValue("@PType", 4);
            SqlDataAdapter myadp = new SqlDataAdapter(mycom);
            DataTable mydt = new DataTable();
            myadp.Fill(mydt);
            return mydt;
        }
    }
    class EmpIdNames
    {
        public string id;
        public string name;

        public EmpIdNames(string id, string name)
        {
            this.id = id;
            this.name = name;
        }
        public string ID
        {
            set
            {
                this.id = value;
            }
            get
            {
                return this.id;
            }
        }
        public string NAME
        {
            set
            {
                this.name = value;
            }
            get
            {
                return name;
            }
        }
    }
}
